package org.yl.db_core.utils.db;

import org.yl.db_core.utils.TypeToCast;
import org.yl.db_core.utils.constant.SqlConstant;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @Description:    jdbc操作数据库
 * @Author:         YL
 * @CreateDate:     2018/11/16 9:52
 * @Version:        1.0
 */
@Component
public class ExecuteJdbcSql {

    private final static Logger logger = LoggerFactory.getLogger(ExecuteJdbcSql.class);

    private final static DBUtil dbUtil = new DBUtil();

    private final static TypeToCast typeToCast = new TypeToCast();

    /**
     * @Description:    查询list
     * @Author:         YL
     * @CreateDate:     2018/11/16 13:49
     * @Version:        1.0
     */
    public List queryForList(String sql, Object object){
        List list = new ArrayList();
        Connection conn = dbUtil.getConnection();
        PreparedStatement ps = dbUtil.getPStatement(conn, sql);
        ResultSet resultSet = null;
        try {
            /**得到实体数据*/
            Map map = typeToCast.convertBeanToMap(object);
            /**得到该类*/
            Class<?> clazzOld = object.getClass();
            Class clazz = Class.forName(clazzOld.getName());
            /**实例化该类*/
            Object obj = null;
            int count = 1;
            /**得到该类所有属性*/
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if (!StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                    toObjectType(ps, map, count, String.valueOf(field.getGenericType()), field.getName());
                }
            }
            count = 1;
            resultSet = ps.executeQuery();
            /**得到查询的总列数*/
            int col = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()){
                obj = clazz.newInstance();
                for (Field field : fields) {
                    PropertyDescriptor descriptor = new PropertyDescriptor(field.getName(), clazz);
                    Method writeMethod = descriptor.getWriteMethod();
                    /**判断属性类型执行数据转换*/
                    toObjectType(writeMethod, obj, resultSet, count, String.valueOf(field.getGenericType()));
                    if (count >= col){
                        count = 1;
                        break;
                    }else {
                        count++;
                    }
                }
                list.add(obj);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbUtil.close(conn, ps, resultSet);
        }
        return null;
    }

    /**
     * @Description: 更新Object
     * @author      YL
     * @return
     * @date        2018/11/16 14:09
     */
    public int insertOrUpdateObject(String sql, Object object, String sqlType){
        int i = 0;
        Connection conn = dbUtil.getConnection();
        PreparedStatement ps = null;
        try {
            ps = dbUtil.getPStatement(conn, sql);
            /**得到该类*/
            Class<?> clazzOld = object.getClass();
            Class clazz = Class.forName(clazzOld.getName());
            /**实例化该类*/
            Object obj = clazz.newInstance();
            /**得到该类所有属性*/
            Field[] fields = clazz.getDeclaredFields();
            /**得到实体数据*/
            Map map = typeToCast.convertBeanToMap(object);
            /**赋值*/
            int count = 1;
            for (Field field : fields) {
                PropertyDescriptor descriptor = new PropertyDescriptor(field.getName(), clazz);
                /**得到读取数据的方法*/
                //Method readMethod = descriptor.getReadMethod();
                /**得到数据*/
                //System.out.println(readMethod.invoke(obj));
                //toObjectType(readMethod, obj, ps, count, String.valueOf(field.getGenericType()));

                if (SqlConstant.INSERT.equalsIgnoreCase(sqlType) || SqlConstant.UPDATE.equalsIgnoreCase(sqlType)) {
                    if (!"id".equalsIgnoreCase(field.getName())) {
                        toObjectType(ps, map, count, String.valueOf(field.getGenericType()), field.getName());
                        count++;
                    }else {
                        toObjectType(ps, map, map.size(), String.valueOf(field.getGenericType()), field.getName());
                    }
                }else if (SqlConstant.DELETE.equalsIgnoreCase(sqlType)) {
                    if (!StringUtils.isEmpty(String.valueOf(map.get(field.getName())))){
                        toObjectType(ps, map, count, String.valueOf(field.getGenericType()), field.getName());
                        count++;
                    }
                }
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbUtil.close(conn, ps);
        }
        return i;
    }



    /**
     * @Description: 读取或设置对象的值
     * @author      YL
     * @return
     * @date        2018/11/16 13:21
     */
    public Object getObjectValue(Object object){
        try {
            /**得到该类*/
            Class<?> clazzOld = object.getClass();
            Class clazz = Class.forName(clazzOld.getName());
            /**实例化该类*/
            Object obj = clazz.newInstance();
            /**得到该类所有属性*/
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                PropertyDescriptor descriptor = new PropertyDescriptor(field.getName(), clazz);
                /**得到读取数据的方法*/
                Method readMethod = descriptor.getReadMethod();
                /**得到数据*/
                readMethod.invoke(obj);

                /**写入数据*/
                Method writeMethod = descriptor.getWriteMethod();
                writeMethod.invoke(obj, "");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * @Description:    查询写入数据到对象
     * @Author:         YL
     * @CreateDate:     2018/11/16 13:48
     * @Version:        1.0
     */
    private void toObjectType(Method method, Object obj, ResultSet resultSet, int count, String type) {
        try {
            /**判断属性类型执行数据转换*/
            if ("class java.lang.String".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getString(count));
            } else if ("class java.lang.Integer".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getInt(count));
            } else if ("class java.lang.Double".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getDouble(count));
            } else if ("class java.lang.Boolean".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getBoolean(count));
            } else if ("class java.lang.Short".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getShort(count));
            } else if ("class java.util.Date".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getDate(count));
            } else if ("class java.lang.long".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getLong(count));
            } else if ("int".equalsIgnoreCase(String.valueOf(type))) {
                method.invoke(obj, resultSet.getInt(count));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * @Description:    增删改读取对象数据 -- 暂时不可用无法读取数据
     * @Author:         YL
     * @CreateDate:     2018/11/16 13:48
     * @Version:        1.0
     */
    private void toObjectType(Method method, Object obj, PreparedStatement ps, int count, String type) {
        try {
            /**判断属性类型执行数据转换*/
            if (method.invoke(obj) != null) {
                if ("class java.lang.String".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setString(count, String.valueOf(method.invoke(obj)));
                } else if ("class java.lang.Integer".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setInt(count, Integer.parseInt(String.valueOf(method.invoke(obj))));
                } else if ("class java.lang.Double".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setDouble(count, Double.valueOf(String.valueOf(method.invoke(obj))));
                } else if ("class java.lang.Boolean".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setBoolean(count, Boolean.valueOf(String.valueOf(method.invoke(obj))));
                } else if ("class java.lang.Short".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setShort(count, Short.valueOf(String.valueOf(method.invoke(obj))));
                } else if ("class java.util.Date".equalsIgnoreCase(String.valueOf(type))) {
                    DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    ps.setDate(count, Date.valueOf(String.valueOf(df.format(new java.util.Date()))));
                } else if ("class java.lang.long".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setLong(count, Long.valueOf(String.valueOf(method.invoke(obj))));
                } else if ("int".equalsIgnoreCase(String.valueOf(type))) {
                    ps.setInt(count, Integer.parseInt(String.valueOf(method.invoke(obj))));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * @Description:    增删改读取对象数据
     * @Author:         YL
     * @CreateDate:     2018/11/16 13:48
     * @Version:        1.0
     */
    private void toObjectType(PreparedStatement ps, Map map, int count, String type, String fileName) {
        try {
            /**判断属性类型执行数据转换*/
            if ("class java.lang.String".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setString(count, String.valueOf(map.get(fileName)));
                }else{
                    ps.setString(count, "");
                }
            } else if ("class java.lang.Integer".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setInt(count, Integer.parseInt(String.valueOf(map.get(fileName))));
                }else{
                    ps.setInt(count, 0);
                }
            } else if ("class java.lang.Double".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setDouble(count, Double.valueOf(String.valueOf(map.get(fileName))));
                }else{
                    ps.setDouble(count, 0);
                }
            } else if ("class java.lang.Boolean".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setBoolean(count, Boolean.valueOf(String.valueOf(map.get(fileName))));
                }else{
                    ps.setBoolean(count, false);
                }
            } else if ("class java.lang.Short".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setShort(count, Short.valueOf(String.valueOf(map.get(fileName))));
                }else{
                    ps.setShort(count, (short) 0);
                }
            } else if ("class java.util.Date".equalsIgnoreCase(String.valueOf(type))) {
                ps.setDate(count, new Date(new java.util.Date().getTime()));
            } else if ("class java.lang.long".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setLong(count, Long.valueOf(String.valueOf(map.get(fileName))));
                }else{
                    ps.setLong(count, (long) 0);
                }
            } else if ("int".equalsIgnoreCase(String.valueOf(type))) {
                if (StringUtils.isNotBlank(String.valueOf(map.get(fileName)))) {
                    ps.setInt(count, Integer.parseInt(String.valueOf(map.get(fileName))));
                }else{
                    ps.setInt(count, 0);
                }
            }
        }catch (Exception e){
            logger.info("判断属性类型执行数据转换==ERROR=【"+e.getMessage()+"】");
            //e.printStackTrace();
        }
    }

}
